# -*- coding: utf-8 -*-

import os, sys, time, datetime

from PyQt4 import QtCore, QtGui

from gui import Ui_MainWindow
import SQLiteDB
#!umbenennen
from guiNewFileSettingsDialog import Ui_Dialog
from guiAddDialog import Ui_Dialog as Ui_AddDialog

ProgName = u'Luckerbitch!'

#try to find the directory for app data, appdata in windows, home in linux
try:
    AppDataPath = os.environ[u'APPDATA']
except:
    AppDataPath = os.environ[u'HOME']

#check if a settingsdir already exists and if not create it
try:
    os.chdir(AppDataPath+os.sep+'.'+ProgName)
    try:
        os.chdir(AppDataPath+os.sep+u'.'+ProgName+os.sep+u'tempdbs')
    except:
        os.mkdir(AppDataPath+os.sep+u'.'+ProgName+os.sep+u'tempdbs')
        os.chdir(AppDataPath+os.sep+u'.'+ProgName)
except:
    os.mkdir(AppDataPath+os.sep+u'.'+ProgName)
    os.mkdir(AppDataPath+os.sep+u'.'+ProgName+os.sep+u'tempdbs')
    os.chdir(AppDataPath+os.sep+u'.'+ProgName)
SettingsDir = AppDataPath+os.sep+u'.'+ProgName+os.sep
TempDBPath = SettingsDir +u'tempdbs'+os.sep



class MainWindow(QtGui.QMainWindow):
    #the Main Window
    def __init__(self):
        #initializes the main window
        QtGui.QMainWindow.__init__(self)
        self.ui=Ui_MainWindow()
        self.ui.setupUi(self)
        self.setWindowTitle(QtGui.QApplication.translate(u"MainWindow", ProgName+u" GUI-Test", None, QtGui.QApplication.UnicodeUTF8))
        self.DB = SQLiteDB.SQLiteDB()
        self.settings = QtCore.QSettings(u'bg',ProgName)
        
        #gives the menu items functionality
        self.connect(self.ui.actionNew, QtCore.SIGNAL(u'triggered()'), self.ShowNewFileDialog)
        self.connect(self.ui.actionOpen, QtCore.SIGNAL(u'triggered()'), self.ShowOpenFileDialog)
        self.connect(self.ui.actionSave, QtCore.SIGNAL(u'triggered()'), self.SaveFunction)
        self.connect(self.ui.actionSaveAs, QtCore.SIGNAL(u'triggered()'), self.ShowSaveAsFileDialog)
        self.connect(self.ui.actionSettings, QtCore.SIGNAL(u'triggered()'), self.ShowSettingsDialog)
        
        #connect the buttons in the tabs
        #save changes
        self.connect(self.ui.PushButtonSaveChangesBookies,QtCore.SIGNAL(u'clicked()'), self.SaveRowChanges)
        self.connect(self.ui.PushButtonSaveChangesTransactions,QtCore.SIGNAL(u'clicked()'), self.SaveRowChanges)
        self.connect(self.ui.PushButtonSaveChangesBets,QtCore.SIGNAL(u'clicked()'), self.SaveRowChanges)
        self.connect(self.ui.PushButtonSaveChangesPicks,QtCore.SIGNAL(u'clicked()'), self.SaveRowChanges)
        self.connect(self.ui.PushButtonSaveChangesCategories,QtCore.SIGNAL(u'clicked()'), self.SaveRowChanges)
        self.connect(self.ui.PushButtonSaveChangesSportsBetTypes,QtCore.SIGNAL(u'clicked()'), self.SaveRowChanges)
        self.connect(self.ui.PushButtonSaveChangesSportsLeagues,QtCore.SIGNAL(u'clicked()'), self.SaveRowChanges)
        self.connect(self.ui.PushButtonSaveChangesBetTypes,QtCore.SIGNAL(u'clicked()'), self.SaveRowChanges)
        #reset changes
        self.connect(self.ui.PushButtonResetChangesBookies,QtCore.SIGNAL(u'clicked()'), self.ResetRowChanges)
        self.connect(self.ui.PushButtonResetChangesTransactions,QtCore.SIGNAL(u'clicked()'), self.ResetRowChanges)
        self.connect(self.ui.PushButtonResetChangesBets,QtCore.SIGNAL(u'clicked()'), self.ResetRowChanges)
        self.connect(self.ui.PushButtonResetChangesPicks,QtCore.SIGNAL(u'clicked()'), self.ResetRowChanges)
        self.connect(self.ui.PushButtonResetChangesCategories,QtCore.SIGNAL(u'clicked()'), self.ResetRowChanges)
        self.connect(self.ui.PushButtonResetChangesSportsBetTypes,QtCore.SIGNAL(u'clicked()'), self.ResetRowChanges)
        self.connect(self.ui.PushButtonResetChangesSportsLeagues,QtCore.SIGNAL(u'clicked()'), self.ResetRowChanges)
        self.connect(self.ui.PushButtonResetChangesBetTypes,QtCore.SIGNAL(u'clicked()'), self.ResetRowChanges)
        #add new
        self.connect(self.ui.PushButtonBookiesAdd,QtCore.SIGNAL(u'clicked()'), self.BookiesAdd)
        self.connect(self.ui.PushButtonTransactionsAdd,QtCore.SIGNAL(u'clicked()'), self.TransactionsAdd)
        self.connect(self.ui.PushButtonBetsAdd,QtCore.SIGNAL(u'clicked()'), self.BetsAdd)
        self.connect(self.ui.PushButtonPicksAdd,QtCore.SIGNAL(u'clicked()'), self.PicksAdd)
        self.connect(self.ui.PushButtonCategoriesAdd,QtCore.SIGNAL(u'clicked()'), self.CategoriesAdd)
        self.connect(self.ui.PushButtonSportsBetTypesAdd,QtCore.SIGNAL(u'clicked()'), self.SportsBetTypesAdd)
        self.connect(self.ui.PushButtonSportsLeaguesAdd,QtCore.SIGNAL(u'clicked()'), self.SportsLeaguesAdd)
        self.connect(self.ui.PushButtonBetTypesAdd,QtCore.SIGNAL(u'clicked()'), self.BetTypesAdd)
        
        #connects item changes so changes can be shown
        self.connect(self.ui.TableBookies,QtCore.SIGNAL(u'itemChanged(QTableWidgetItem*)'),self.ItemChangedBookies)
        self.connect(self.ui.TableTransactions,QtCore.SIGNAL(u'itemChanged(QTableWidgetItem*)'),self.ItemChangedTransactions)
        self.connect(self.ui.TableBets,QtCore.SIGNAL(u'itemChanged(QTableWidgetItem*)'),self.ItemChangedBets)
        self.connect(self.ui.TablePicks,QtCore.SIGNAL(u'itemChanged(QTableWidgetItem*)'),self.ItemChangedPicks)
        self.connect(self.ui.TableCategories,QtCore.SIGNAL(u'itemChanged(QTableWidgetItem*)'),self.ItemChangedCategories)
        self.connect(self.ui.TableSportsLeagues,QtCore.SIGNAL(u'itemChanged(QTableWidgetItem*)'),self.ItemChangedSportsLeagues)
        self.connect(self.ui.TableSportsBetTypes,QtCore.SIGNAL(u'itemChanged(QTableWidgetItem*)'),self.ItemChangedSportsBetTypes)
        self.connect(self.ui.TableBetTypes,QtCore.SIGNAL(u'itemChanged(QTableWidgetItem*)'),self.ItemChangedBetTypes)
        #self.connect(self.ui.TableBookies.horizontalHeader(),QtCore.SIGNAL('sectionClicked (int)'),self.SortColumn)
        
        #makes Save and Save as menus unclickable
        self.ui.actionSave.setEnabled(False)
        self.ui.actionSaveAs.setEnabled(False)
        self.RestoreMainWindowSettings()
        #checks if the Save as and Settings menu should be available (only if there is a open db file)
        self.CheckSaveAsAndSettingsEnable()
        
        #tables
        self.ui.TableBookies.hideColumn(0)
        self.ui.TableBookies.hideColumn(9)
        self.ui.TableBookies.hideColumn(10)
        self.ui.TableTransactions.hideColumn(0)
        self.ui.TableBets.hideColumn(0)
        self.ui.TableBets.hideColumn(2)
        self.ui.TablePicks.hideColumn(0)
        self.ui.TableCategories.hideColumn(0)
        self.ui.TableSportsLeagues.hideColumn(0)
        self.ui.TableSportsBetTypes.hideColumn(0)
        self.ui.TableBetTypes.hideColumn(0)
        
        #self.GetDataAll()
        #print self.tmpBets
        
        
    def CorrectPathToFile(self,path):
        #converts the filepath to string and adds correct separators for windows
        path = unicode(path)
        if path[0] != u'/':
            l = path.split(u'/')
            x = 0
            ll = []
            while x < len(l):
                ll.append(l[x])
                if len(l)-1 > x:
                    ll.append(u'\\')
                x +=1
            path = ''
            for i in ll:
                path = path + i
            return path
        return path
        
    def ShowWidget(self,w):
        #shows widget w
        #self.HideWidgets()
        self.visibleWidget=w
        w.show()
        
    def ShowWidgetExec(self,w):
        #same as ShowWidget for widgets with exec_()
        self.visibleWidget=w
         
    def ShowNewFileDialog(self):
        #opens a Save File Dialog with Home/Userprofile (Linux/windows) dir as shown directory and opens the dialog to edit settings for the new file
        #if the filename the dialog returns is not '' which is it when the user clicks cancel
        try:
            filename = QtGui.QFileDialog.getSaveFileName(self, u'New file', os.environ[u'HOME'],u"SQLite files (*.sqlite);;All Files (*)")
        except KeyError:
            filename = QtGui.QFileDialog.getSaveFileName(self, u'New file', os.environ[u'USERPROFILE'],u"SQLite files (*.sqlite);;All Files (*)")
        if filename != '':
            NewSQLiteDB = filename
            self.ShowWidgetExec(NewDialog(NewSQLiteDB))
            #checks if a (new) file was opened an the Save as and Settings menus should be available and creates the new db
            if self.CheckSaveAsAndSettingsEnable() == True:
                #make a temporary copy of the current ramdb
                #datetimestring = datetime.datetime.strftime(datetime.datetime.now(), u'%Y-%m-%d-%H-%M-%S')
                #self.DB.CopyDB(TempDBPath+datetimestring+u'.sqlite')
                
                #close connection and reinit in case there already is a ramdb
                self.DB.CloseConnection(self.DB.ramdb)
                self.DB.__init__()
                #makes tables and inserts necessary data
                self.DB.MakeTables()
                self.DB.CategoryETCData()
                #fill with testdata
                #self.DB.TestData()
                self.GetDataAll()
                self.PaintAllData()
                self.RepaintAllData()
                #self.PaintData(self.tmpBookies,self.ui.TableBookies)
                #self.PaintData(self.tmpTransactions,self.ui.TableTransactions)
                #self.PaintData(self.tmpBets,self.ui.TableBets)
                #self.PaintData(self.tmpPicks,self.ui.TablePicks)
                #self.PaintData(self.tmpCategories,self.ui.TableCategories)
                #self.PaintData(self.tmpSportsLeagues,self.ui.TableSportsLeagues)
                #self.PaintData(self.tmpSportsBetTypes,self.ui.TableSportsBetTypes)
                #self.PaintData(self.tmpBetTypes,self.ui.TableBetTypes)
                #saves the ramdb to disk
                self.SaveFunction()
                
        
    def ShowOpenFileDialog(self):
        #opens a Open File Dialog with Home/Userprofile (Linux/windows) dir as shown directory and makes the opened file the ActiveSQLiteDB if not ''
        try:
            filename = QtGui.QFileDialog.getOpenFileName(self, u'Open file', os.environ[u'HOME'],u"SQLite files (*.sqlite);;All Files (*)")
        except KeyError:
            filename = QtGui.QFileDialog.getOpenFileName(self, u'Open file', os.environ[u'USERPROFILE'],u"SQLite files (*.sqlite);;All Files (*)")
        if filename != u'':
            global ActiveSQLiteDB
            ActiveSQLiteDB = self.CorrectPathToFile(filename)
            #!add functionality for encrypted db
            
            #make a temporary copy of the current ramdb
            #datetimestring = datetime.datetime.strftime(datetime.datetime.now(), u'%Y-%m-%d-%H-%M-%S')
            #self.DB.CopyDB(TempDBPath+datetimestring+u'.sqlite')
            
            self.DB.CloseConnection(self.DB.ramdb)
            self.DB.__init__()
            self.DB.CopyDB(ActiveSQLiteDB,None,False)
            self.GetDataAll()
            self.RepaintAllData()
            self.CheckSaveAsAndSettingsEnable()
            
    def SaveFunction(self):
        #Saves the current ramdb to the specified disk db
        self.DB.CopyDB(self.CorrectPathToFile(ActiveSQLiteDB))
        print u'saved'

        
    def ShowSaveAsFileDialog(self):
        #shows a save file dialog, the specified file will be the new ActiveSQLiteDB and be a copy of the ramdb
        try:
            filename = QtGui.QFileDialog.getSaveFileName(self, u'Open file', os.environ[u'HOME'],u"SQLite files (*.sqlite);;All Files (*)")
        except KeyError:
            filename = QtGui.QFileDialog.getSaveFileName(self, u'Open file', os.environ[u'USERPROFILE'],u"SQLite files (*.sqlite);;All Files (*)")
        if filename != '':
            global ActiveSQLiteDB
            ActiveSQLiteDB = self.CorrectPathToFile(filename)
            self.DB.CopyDB(ActiveSQLiteDB)
            
    
    def ShowSettingsDialog(self):
        #Shows the Dialog to change the Password settings of the current DB
        #!add settingsfunctions
        self.ShowWidget(SettingsDialog(ActiveSQLiteDB))
        
    def CheckSaveAsAndSettingsEnable(self):
        #Checks if a disk DB File is specified and makes the settings and save as menu available
        try:
            if ActiveSQLiteDB != u'':
                self.ui.actionSettings.setEnabled(True)
                self.ui.actionSaveAs.setEnabled(True)
                self.ui.actionSave.setEnabled(True)
                return True
            else:
                self.ui.actionSettings.setEnabled(False)
                self.ui.actionSaveAs.setEnabled(False)
                self.ui.actionSave.setEnabled(False)
        except:
            self.ui.actionSettings.setEnabled(False)
            self.ui.actionSaveAs.setEnabled(False)
            self.ui.actionSave.setEnabled(False)
            
    def SaveMainWindowSettings(self):
        #saves the window settings for reload at new start
        self.settings.setValue(u'geometry',self.saveGeometry())
        self.settings.setValue(u'state',self.saveState())
        self.settings.sync()
        
    def RestoreMainWindowSettings(self):
        #restores the saved window settings
        self.restoreGeometry(self.settings.value(u'geometry').toByteArray())
        self.restoreState(self.settings.value(u'state').toByteArray())
        
    def GetDataAll(self):
        #gets data from all tables and tempstores it
        self.tmpBookies = self.DB.GetData(u'bookies')
        self.tmpTransactions = self.DB.GetData(u'transactions')
        self.tmpBets = self.DB.GetData(u'bets')
        self.tmpPicks = self.DB.GetData(u'picks')
        self.tmpCategories = self.DB.GetData(u'categories')
        self.tmpSportsLeagues = self.DB.GetData(u'sportsleagues')
        self.tmpSportsBetTypes = self.DB.GetData(u'sportsbettypes')
        self.tmpBetTypes = self.DB.GetData(u'bettypes')
        for i in self.tmpBookies:
            bookieid = int(i[0])
            whereTransactions = self.DB.GetData(u'transactions',u'bookieid',unicode(bookieid))
            balance = 0.00
            revenue = 0.00
            profit = 0.00
            for ii in whereTransactions:
                balance = balance + float(ii[3])
                if ii[4] != u'Kontostand' and ii[4] != u'Einzahlung' and ii[4] != u'Auszahlung' and ii[4] != u'Bonus':
                    revenue = revenue + abs(float(ii[3]))
                    profit = profit + float(ii[3])
            cursor = self.DB.ramdb.cursor()
            textindex = (unicode(balance),bookieid,)
            cursor.execute(u'''update '''+u'bookies'+''' set '''+unicode(u'balance'+u' = ?')+''' where '''+unicode(u'bookieid'+' = ?' ),textindex)
            textindex = (unicode(revenue),bookieid,)
            cursor.execute(u'''update '''+u'bookies'+''' set '''+unicode(u'revenue'+u' = ?')+''' where '''+unicode(u'bookieid'+' = ?' ),textindex)
            textindex = (unicode(profit),bookieid,)
            cursor.execute(u'''update '''+u'bookies'+''' set '''+unicode(u'profit'+u' = ?')+''' where '''+unicode(u'bookieid'+' = ?' ),textindex)
            self.DB.ramdb.commit()
            cursor.close
        self.tmpBookies = self.DB.GetData(u'bookies')
            
        
    def PaintData(self,tmpdata,table):
        #takes the tmpdata tuple and inserts the values into the table
        #for bets and picks tables coloring the background depending on result
        if table == self.ui.TableBets:
            x = 0
            for i in tmpdata:
                table.insertRow(x)
                xx = 0
                for ii in i:
                    #result is the 10th column of the bets table --> index 9
                    if i[9] == 1:
                        a = QtGui.QTableWidgetItem()
                        a.setText(unicode(ii))
                        a.setBackground(QtGui.QBrush(QtCore.Qt.GlobalColor(QtCore.Qt.green)))
                        table.setItem(x,xx,a)
                        #a.setFlags(QtCore.Qt.ItemIsSelectable | QtCore.Qt.ItemIsEnabled)
                        xx +=1
                    elif i[9] == 0:
                        a = QtGui.QTableWidgetItem()
                        a.setText(unicode(ii))
                        a.setBackground(QtGui.QBrush(QtCore.Qt.GlobalColor(QtCore.Qt.red)))
                        table.setItem(x,xx,a)
                        #a.setFlags(QtCore.Qt.ItemIsSelectable | QtCore.Qt.ItemIsEnabled)
                        xx +=1
                    elif i[9] == 2:
                        a = QtGui.QTableWidgetItem()
                        a.setText(unicode(ii))
                        a.setBackground(QtGui.QBrush(QtCore.Qt.GlobalColor(QtCore.Qt.blue)))
                        table.setItem(x,xx,a)
                        #a.setFlags(QtCore.Qt.ItemIsSelectable | QtCore.Qt.ItemIsEnabled)
                        xx +=1
                    else:
                        a = QtGui.QTableWidgetItem()
                        a.setText(unicode(ii))
                        table.setItem(x,xx,a)
                        #a.setFlags(QtCore.Qt.ItemIsSelectable | QtCore.Qt.ItemIsEnabled)
                        xx +=1
                x +=1
        elif table == self.ui.TablePicks:
            x = 0
            for i in tmpdata:
                table.insertRow(x)
                xx = 0
                for ii in i:
                    #result is the 16th column of the bets table --> index 15
                    if i[15] == 1:
                        a = QtGui.QTableWidgetItem()
                        a.setText(unicode(ii))
                        a.setBackground(QtGui.QBrush(QtCore.Qt.GlobalColor(QtCore.Qt.green)))
                        table.setItem(x,xx,a)
                        #a.setFlags(QtCore.Qt.ItemIsSelectable | QtCore.Qt.ItemIsEnabled)
                        xx +=1
                    elif i[15] == 0:
                        a = QtGui.QTableWidgetItem()
                        a.setText(unicode(ii))
                        a.setBackground(QtGui.QBrush(QtCore.Qt.GlobalColor(QtCore.Qt.red)))
                        table.setItem(x,xx,a)
                        #a.setFlags(QtCore.Qt.ItemIsSelectable | QtCore.Qt.ItemIsEnabled)
                        xx +=1
                    elif i[15] == 2:
                        a = QtGui.QTableWidgetItem()
                        a.setText(unicode(ii))
                        a.setBackground(QtGui.QBrush(QtCore.Qt.GlobalColor(QtCore.Qt.blue)))
                        table.setItem(x,xx,a)
                        #a.setFlags(QtCore.Qt.ItemIsSelectable | QtCore.Qt.ItemIsEnabled)
                        xx +=1
                    else:
                        a = QtGui.QTableWidgetItem()
                        a.setText(unicode(ii))
                        table.setItem(x,xx,a)
                        #a.setFlags(QtCore.Qt.ItemIsSelectable | QtCore.Qt.ItemIsEnabled)
                        xx +=1
                x +=1
        else:
            x = 0
            for i in tmpdata:
                table.insertRow(x)
                xx = 0
                for ii in i:
                    a = QtGui.QTableWidgetItem()
                    a.setText(unicode(ii))
                    table.setItem(x,xx,a)
                    #a.setFlags(QtCore.Qt.ItemIsSelectable | QtCore.Qt.ItemIsEnabled)
                    xx +=1
                x +=1
        
    def PaintAllData(self):
        #shortcut to paint all tables
        self.PaintData(self.tmpBookies,self.ui.TableBookies)
        self.PaintData(self.tmpTransactions,self.ui.TableTransactions)
        self.PaintData(self.tmpBets,self.ui.TableBets)
        self.PaintData(self.tmpPicks,self.ui.TablePicks)
        self.PaintData(self.tmpCategories,self.ui.TableCategories)
        self.PaintData(self.tmpSportsLeagues,self.ui.TableSportsLeagues)
        self.PaintData(self.tmpSportsBetTypes,self.ui.TableSportsBetTypes)
        self.PaintData(self.tmpBetTypes,self.ui.TableBetTypes)
        self.DB.ramdb.rollback()
        
    def RemoveData(self,table):
        #removes the rows from the tables, works for no rows too
        a = table.rowCount()-1
        while table.rowCount() > 0:
            table.removeRow(a)
            a-=1
            
    def RepaintAllData(self):
        #shortcut to remove all rows from all tables and repaint the hole thing
        self.RemoveData(self.ui.TableBookies)
        self.RemoveData(self.ui.TableTransactions)
        self.RemoveData(self.ui.TableBets)
        self.RemoveData(self.ui.TablePicks)
        self.RemoveData(self.ui.TableCategories)
        self.RemoveData(self.ui.TableSportsLeagues)
        self.RemoveData(self.ui.TableSportsBetTypes)
        self.RemoveData(self.ui.TableBetTypes)
        #gets and paints the data again
        self.GetDataAll()
        self.PaintAllData()
        
        
    def SaveRowChanges(self):
        self.DB.ramdb.commit()

    def ResetRowChanges(self):
        self.DB.ramdb.rollback()
        self.RepaintAllData()
        
    def SortColumn(self,i):
        tab = self.ui.tabWidget.currentIndex()
        print i
        if tab == 1:
            self.ui.TableBookies.sortItems(i,QtCore.Qt.DescendingOrder)
            
    def ItemChangedBookies(self,item):
        row = item.row()
        column = item.column()
        if unicode(item.text()) != unicode(self.tmpBookies[row][column]):
            item.setBackground(QtGui.QBrush(QtCore.Qt.GlobalColor(QtCore.Qt.yellow)))
            cursor = self.DB.ramdb.cursor()
            textindex = (unicode(item.text()),row+1,)
            if column == 1:
                cursor.execute(u'''update '''+u'bookies'+''' set '''+unicode(u'name'+u' = ?')+''' where '''+unicode(u'bookieid'+' = ?' ),textindex)
            elif column == 2:
                cursor.execute(u'''update '''+u'bookies'+''' set '''+unicode(u'balance'+u' = ?')+''' where '''+unicode(u'bookieid'+' = ?' ),textindex)
            elif column == 3:
                cursor.execute(u'''update '''+u'bookies'+''' set '''+unicode(u'revenue'+u' = ?')+''' where '''+unicode(u'bookieid'+' = ?' ),textindex)
            elif column == 4:
                cursor.execute(u'''update '''+u'bookies'+''' set '''+unicode(u'profit'+u' = ?')+''' where '''+unicode(u'bookieid'+' = ?' ),textindex)
            elif column == 5:
                cursor.execute(u'''update '''+u'bookies'+''' set '''+unicode(u'hasactivebonus'+u' = ?')+''' where '''+unicode(u'bookieid'+' = ?' ),textindex)
            elif column == 6:
                cursor.execute(u'''update '''+u'bookies'+''' set '''+unicode(u'bonusrevenuecondition'+u' = ?')+''' where '''+unicode(u'bookieid'+' = ?' ),textindex)
            elif column == 7:
                cursor.execute(u'''update '''+u'bookies'+''' set '''+unicode(u'bonusoddscondition'+u' = ?')+''' where '''+unicode(u'bookieid'+' = ?' ),textindex)
            elif column == 8:
                cursor.execute(u'''update '''+u'bookies'+''' set '''+unicode(u'bonusrevenue'+u' = ?')+''' where '''+unicode(u'bookieid'+' = ?' ),textindex)
            elif column == 9:
                cursor.execute(u'''update '''+u'bookies'+''' set '''+unicode(u'username'+u' = ?')+''' where '''+unicode(u'bookieid'+' = ?' ),textindex)
            elif column == 10:
                cursor.execute(u'''update '''+u'bookies'+''' set '''+unicode(u'password'+u' = ?')+''' where '''+unicode(u'bookieid'+' = ?' ),textindex)
            else:
                item.setBackground(QtGui.QBrush(QtCore.Qt.GlobalColor(QtCore.Qt.white)))
        else:
            item.setBackground(QtGui.QBrush(QtCore.Qt.GlobalColor(QtCore.Qt.white)))
            
    def ItemChangedTransactions(self,item):
        row = item.row()
        column = item.column()
        if unicode(item.text()) != unicode(self.tmpTransactions[row][column]):
            item.setBackground(QtGui.QBrush(QtCore.Qt.GlobalColor(QtCore.Qt.yellow)))
            cursor = self.DB.ramdb.cursor()
            textindex = (unicode(item.text()),row+1,)
            if column == 1:
                cursor.execute(u'''update '''+u'transactions'+''' set '''+unicode(u'bookieid'+u' = ?')+''' where '''+unicode(u'transactionid'+' = ?' ),textindex)
            elif column == 2:
                cursor.execute(u'''update '''+u'transactions'+''' set '''+unicode(u'datetime'+u' = ?')+''' where '''+unicode(u'transactionid'+' = ?' ),textindex)
            elif column == 3:
                cursor.execute(u'''update '''+u'transactions'+''' set '''+unicode(u'money'+u' = ?')+''' where '''+unicode(u'transactionid'+' = ?' ),textindex)
            elif column == 4:
                cursor.execute(u'''update '''+u'transactions'+''' set '''+unicode(u'category'+u' = ?')+''' where '''+unicode(u'transactionid'+' = ?' ),textindex)
            else:
                item.setBackground(QtGui.QBrush(QtCore.Qt.GlobalColor(QtCore.Qt.white)))
        else:
            item.setBackground(QtGui.QBrush(QtCore.Qt.GlobalColor(QtCore.Qt.white)))
            
    def ItemChangedBets(self,item):
        row = item.row()
        column = item.column()
        if unicode(item.text()) != unicode(self.tmpBets[row][column]):
            item.setBackground(QtGui.QBrush(QtCore.Qt.GlobalColor(QtCore.Qt.yellow)))
            cursor = self.DB.ramdb.cursor()
            textindex = (unicode(item.text()),row+1,)
            if column == 1:
                cursor.execute(u'''update '''+u'bets'+''' set '''+unicode(u'bookieid'+u' = ?')+''' where '''+unicode(u'betid'+' = ?' ),textindex)
            elif column == 3:
                cursor.execute(u'''update '''+u'bets'+''' set '''+unicode(u'datetime'+u' = ?')+''' where '''+unicode(u'betid'+' = ?' ),textindex)
            elif column == 4:
                cursor.execute(u'''update '''+u'bets'+''' set '''+unicode(u'bettypeid'+u' = ?')+''' where '''+unicode(u'betid'+' = ?' ),textindex)
            elif column == 5:
                cursor.execute(u'''update '''+u'bets'+''' set '''+unicode(u'pickids'+u' = ?')+''' where '''+unicode(u'betid'+' = ?' ),textindex)
            elif column == 6:
                cursor.execute(u'''update '''+u'bets'+''' set '''+unicode(u'category'+u' = ?')+''' where '''+unicode(u'betid'+' = ?' ),textindex)
            elif column == 7:
                cursor.execute(u'''update '''+u'bets'+''' set '''+unicode(u'odds'+u' = ?')+''' where '''+unicode(u'betid'+' = ?' ),textindex)
            elif column == 8:
                cursor.execute(u'''update '''+u'bets'+''' set '''+unicode(u'stake'+u' = ?')+''' where '''+unicode(u'betid'+' = ?' ),textindex)
            elif column == 9:
                cursor.execute(u'''update '''+u'bets'+''' set '''+unicode(u'result'+u' = ?')+''' where '''+unicode(u'betid'+' = ?' ),textindex)
                if int(textindex[0])== 1:
                    transactionid = unicode(self.ui.TableBets.item(row,2).text())
                    if len(transactionid.split(',')) == 1:
                        newtransactionid = len(self.tmpTransactions)+1
                        transactionid = transactionid + u','+unicode(newtransactionid)
                        self.ui.TableBets.item(row,2).setText(unicode(transactionid))
                        bookieid = unicode(self.ui.TableBets.item(row,1).text())
                        datetimew = datetime.datetime.today()
                        odds = float(self.ui.TableBets.item(row,7).text())
                        stake = float(self.ui.TableBets.item(row,8).text())
                        winnings = odds*stake
                        winnings = round(winnings,2)
                        transaction = [(None,bookieid,datetimew,winnings,u'Gewinn')]
                        self.DB.InsertData(u'transactions',transaction)
                        texttid = (unicode(transactionid),row+1)
                        cursor.execute(u'''update '''+u'bets'+''' set '''+unicode(u'transactionids'+u' = ?')+''' where '''+unicode(u'betid'+' = ?' ),texttid)
                        

                    
            elif column == 10:
                cursor.execute(u'''update '''+u'bets'+''' set '''+unicode(u'return'+u' = ?')+''' where '''+unicode(u'betid'+' = ?' ),textindex)
                
    def ItemChangedPicks(self,item):
        row = item.row()
        column = item.column()
        if unicode(item.text()) != unicode(self.tmpPicks[row][column]):
            item.setBackground(QtGui.QBrush(QtCore.Qt.GlobalColor(QtCore.Qt.yellow)))
            cursor = self.DB.ramdb.cursor()
            textindex = (unicode(item.text()),row+1,)
            if column == 1:
                cursor.execute(u'''update '''+u'picks'+''' set '''+unicode(u'bookieid'+u' = ?')+''' where '''+unicode(u'pickid'+' = ?' ),textindex)
            elif column == 2:
                cursor.execute(u'''update '''+u'picks'+''' set '''+unicode(u'datetime'+u' = ?')+''' where '''+unicode(u'pickid'+' = ?' ),textindex)
            elif column == 3:
                cursor.execute(u'''update '''+u'picks'+''' set '''+unicode(u'datetime_begin'+u' = ?')+''' where '''+unicode(u'pickid'+' = ?' ),textindex)
            elif column == 4:
                cursor.execute(u'''update '''+u'picks'+''' set '''+unicode(u'category'+u' = ?')+''' where '''+unicode(u'pickid'+' = ?' ),textindex)
            elif column == 5:
                cursor.execute(u'''update '''+u'picks'+''' set '''+unicode(u'sport'+u' = ?')+''' where '''+unicode(u'pickid'+' = ?' ),textindex)
            elif column == 6:
                cursor.execute(u'''update '''+u'picks'+''' set '''+unicode(u'league'+u' = ?')+''' where '''+unicode(u'pickid'+' = ?' ),textindex)
            elif column == 7:
                cursor.execute(u'''update '''+u'picks'+''' set '''+unicode(u'hometeam'+u' = ?')+''' where '''+unicode(u'pickid'+' = ?' ),textindex)
            elif column == 8:
                cursor.execute(u'''update '''+u'picks'+''' set '''+unicode(u'awayteam'+u' = ?')+''' where '''+unicode(u'pickid'+' = ?' ),textindex)
            elif column == 9:
                cursor.execute(u'''update '''+u'picks'+''' set '''+unicode(u'picktype'+u' = ?')+''' where '''+unicode(u'pickid'+' = ?' ),textindex)
            elif column == 10:
                cursor.execute(u'''update '''+u'picks'+''' set '''+unicode(u'player'+u' = ?')+''' where '''+unicode(u'pickid'+' = ?' ),textindex)
            elif column == 11:
                cursor.execute(u'''update '''+u'picks'+''' set '''+unicode(u'pick'+u' = ?')+''' where '''+unicode(u'pickid'+' = ?' ),textindex)
            elif column == 12:
                cursor.execute(u'''update '''+u'picks'+''' set '''+unicode(u'odds'+u' = ?')+''' where '''+unicode(u'pickid'+' = ?' ),textindex)
            elif column == 13:
                cursor.execute(u'''update '''+u'picks'+''' set '''+unicode(u'odds_multi'+u' = ?')+''' where '''+unicode(u'pickid'+' = ?' ),textindex)
            elif column == 14:
                cursor.execute(u'''update '''+u'picks'+''' set '''+unicode(u'reason'+u' = ?')+''' where '''+unicode(u'pickid'+' = ?' ),textindex)
            elif column == 15:
                cursor.execute(u'''update '''+u'picks'+''' set '''+unicode(u'result'+u' = ?')+''' where '''+unicode(u'pickid'+' = ?' ),textindex)
            elif column == 16:
                cursor.execute(u'''update '''+u'picks'+''' set '''+unicode(u'score'+u' = ?')+''' where '''+unicode(u'pickid'+' = ?' ),textindex)
                
    def ItemChangedCategories(self,item):
        row = item.row()
        column = item.column()
        if unicode(item.text()) != unicode(self.tmpCategories[row][column]):
            item.setBackground(QtGui.QBrush(QtCore.Qt.GlobalColor(QtCore.Qt.yellow)))
            cursor = self.DB.ramdb.cursor()
            textindex = (unicode(item.text()),row+1,)
            if column == 1:
                cursor.execute(u'''update '''+u'categories'+''' set '''+unicode(u'category'+u' = ?')+''' where '''+unicode(u'categoryid'+' = ?' ),textindex)
            else:
                item.setBackground(QtGui.QBrush(QtCore.Qt.GlobalColor(QtCore.Qt.white)))
        else:
            item.setBackground(QtGui.QBrush(QtCore.Qt.GlobalColor(QtCore.Qt.white)))
            
    def ItemChangedSportsLeagues(self,item):
        row = item.row()
        column = item.column()
        if unicode(item.text()) != unicode(self.tmpSportsLeagues[row][column]):
            item.setBackground(QtGui.QBrush(QtCore.Qt.GlobalColor(QtCore.Qt.yellow)))
            cursor = self.DB.ramdb.cursor()
            textindex = (unicode(item.text()),row+1,)
            if column == 1:
                cursor.execute(u'''update '''+u'sportsleagues'+''' set '''+unicode(u'sport'+u' = ?')+''' where '''+unicode(u'sportleagueid'+' = ?' ),textindex)
            elif column == 2:
                cursor.execute(u'''update '''+u'sportsleagues'+''' set '''+unicode(u'league'+u' = ?')+''' where '''+unicode(u'sportleagueid'+' = ?' ),textindex)
            else:
                item.setBackground(QtGui.QBrush(QtCore.Qt.GlobalColor(QtCore.Qt.white)))
        else:
            item.setBackground(QtGui.QBrush(QtCore.Qt.GlobalColor(QtCore.Qt.white)))
            
    def ItemChangedSportsBetTypes(self,item):
        row = item.row()
        column = item.column()
        if unicode(item.text()) != unicode(self.tmpSportsBetTypes[row][column]):
            item.setBackground(QtGui.QBrush(QtCore.Qt.GlobalColor(QtCore.Qt.yellow)))
            cursor = self.DB.ramdb.cursor()
            textindex = (unicode(item.text()),row+1,)
            if column == 1:
                cursor.execute(u'''update '''+u'sportsbettypes'+''' set '''+unicode(u'sport'+u' = ?')+''' where '''+unicode(u'sportbettypeid'+' = ?' ),textindex)
            elif column == 2:
                cursor.execute(u'''update '''+u'sportsbettypes'+''' set '''+unicode(u'bettype'+u' = ?')+''' where '''+unicode(u'sportbettypeid'+' = ?' ),textindex)
            else:
                item.setBackground(QtGui.QBrush(QtCore.Qt.GlobalColor(QtCore.Qt.white)))
        else:
            item.setBackground(QtGui.QBrush(QtCore.Qt.GlobalColor(QtCore.Qt.white)))
            
    def ItemChangedBetTypes(self,item):
        row = item.row()
        column = item.column()
        if unicode(item.text()) != unicode(self.tmpBetTypes[row][column]):
            item.setBackground(QtGui.QBrush(QtCore.Qt.GlobalColor(QtCore.Qt.yellow)))
            cursor = self.DB.ramdb.cursor()
            textindex = (unicode(item.text()),row+1,)
            if column == 1:
                cursor.execute(u'''update '''+u'bettypes'+''' set '''+unicode(u'bettype'+u' = ?')+''' where '''+unicode(u'bettypeid'+' = ?' ),textindex)
            else:
                item.setBackground(QtGui.QBrush(QtCore.Qt.GlobalColor(QtCore.Qt.white)))
        else:
            item.setBackground(QtGui.QBrush(QtCore.Qt.GlobalColor(QtCore.Qt.white)))
            
    def BookiesAdd(self):
        self.ShowWidgetExec(AddDialog(0,self.tmpTransactions,self.tmpPicks))
        if added == 1:
            self.DB.InsertData(u'bookies',AddBookies)
        self.RepaintAllData()
        
    def TransactionsAdd(self):
        self.ShowWidgetExec(AddDialog(1,self.tmpTransactions,self.tmpPicks))
        if added == 1:
            self.DB.InsertData(u'transactions',AddTransactions)
        self.RepaintAllData()
        
    def BetsAdd(self):
        self.ShowWidgetExec(AddDialog(2,self.tmpTransactions,self.tmpPicks))
        if added == 1:
            self.DB.InsertData(u'bets',AddBets)
            self.DB.InsertData(u'picks',AddPicks)
            self.DB.InsertData(u'transactions',AddTransactions)
        self.RepaintAllData()
        
    def PicksAdd(self):
        self.ShowWidgetExec(AddDialog(3,self.tmpTransactions,self.tmpPicks))
        if added == 1:
            self.DB.InsertData(u'picks',AddPicks)
        self.RepaintAllData()
        
    def CategoriesAdd(self):
        self.ShowWidgetExec(AddDialog(4,self.tmpTransactions,self.tmpPicks))
        if added == 1:
            self.DB.InsertData(u'categories',AddCategories)
        self.RepaintAllData()
        
    def SportsLeaguesAdd(self):
        self.ShowWidgetExec(AddDialog(5,self.tmpTransactions,self.tmpPicks))
        if added == 1:
            self.DB.InsertData(u'sportsleagues',AddSportsLeagues)
        self.RepaintAllData()
        
    def SportsBetTypesAdd(self):
        self.ShowWidgetExec(AddDialog(6,self.tmpTransactions,self.tmpPicks))
        if added == 1:
            self.DB.InsertData(u'sportsbettypes',AddSportsBetTypes)
        self.RepaintAllData()
        
    def BetTypesAdd(self):
        self.ShowWidgetExec(AddDialog(7,self.tmpTransactions,self.tmpPicks))
        if added == 1:
            self.DB.InsertData(u'bettypes',AddBetTypes)
        self.RepaintAllData()


class NewDialog(QtGui.QDialog,):
#the dialog to change settings for a new db file
    def __init__(self, NewSQLiteDB):
        #initializes the dialog
        QtGui.QDialog.__init__(self)
        self.ui=Ui_Dialog()
        self.ui.setupUi(self)
        #modal because working in the main window makes no sense until the file has its settings set
        self.setModal(True)
        self.setWindowTitle(u'Einstellungen fuer neue Datenbank')
           
        #makes the password lines write only (shows stars/circles) and them and the other options inactive (greyed out) until the password checkbox is checked
        self.ui.LineEditPassword.setEchoMode(2)
        self.ui.LineEditConfirmPassword.setEchoMode(2)
        self.ui.LabelPassword.setEnabled(False)
        self.ui.LabelConfirmPassword.setEnabled(False)
        self.ui.LineEditPassword.setEnabled(False)
        self.ui.LineEditConfirmPassword.setEnabled(False)
        self.ui.LabelCheckPasswordMatch.setEnabled(False)
        self.ui.CheckBoxShowPassword.setEnabled(False)
        
        #gives the change file button, encrypt db checkbox, show passwords checkbox functionality
        self.connect(self.ui.PushButtonChangeFile,QtCore.SIGNAL(u"clicked()"),self.ShowNewFileDialog)
        self.connect(self.ui.CheckBoxEncrypt,QtCore.SIGNAL(u"clicked()"),self.EncryptOptions)
        self.connect(self.ui.CheckBoxShowPassword,QtCore.SIGNAL(u"clicked()"),self.ShowPassword)
        
        #checks if the passwords match whenever one of the password lines is changed
        self.connect(self.ui.LineEditPassword,QtCore.SIGNAL(u"textEdited(QString)"),self.CheckPasswordMatch)
        self.connect(self.ui.LineEditConfirmPassword,QtCore.SIGNAL(u"textEdited(QString)"),self.CheckPasswordMatch)
        
        #if accepted (OK clicked) gives back the options
        self.connect(self,QtCore.SIGNAL(u"accepted()"),self.Accepted)
        
        #funtionality to show the current new file for which the settings are changed
        self.NewFile(NewSQLiteDB)
        
        #exec_() so the check if the Settings menu should be available is run only after the dialog is finished
        self.exec_()
        
    def ShowNewFileDialog(self):
        #opens a save file dialog with which the new file can be changed
        try:
            filename = QtGui.QFileDialog.getSaveFileName(self, u'Open file', os.environ[u'HOME'],u"SQLite files (*.sqlite);;All Files (*)")
        except KeyError:
            filename = QtGui.QFileDialog.getSaveFileName(self, u'Open file', os.environ[u'USERPROFILE'],u"SQLite files (*.sqlite);;All Files (*)")
        if filename != '':
            NewSQLiteDB = filename
            self.NewFile(NewSQLiteDB)
        
    def NewFile(self,NewSQLiteDB):
        #check whether win or linux (file location beginng with '/...' linux and show the location in the read only Line Edit,
        #if windows change the directory separator from / to \
        if NewSQLiteDB[0] != u'/':
            l = NewSQLiteDB.split(u'/')
            x = 0
            ll = []
            while x < len(l):
                ll.append(l[x])
                if len(l)-1 > x:
                    ll.append(os.sep)
                x +=1
            NewSQLiteDB = u''
            for i in ll:
                NewSQLiteDB = NewSQLiteDB + i
            self.ui.LineEditFile.setText(NewSQLiteDB)
            self.ui.LineEditFile.setReadOnly(True)
        else:
            self.ui.LineEditFile.setText(NewSQLiteDB)
            self.ui.LineEditFile.setReadOnly(True)
    
    def EncryptOptions(self):
        #makes the password options only active if the encrypt checkbox is checked and checks whether to make ok/cancel available
        if self.ui.CheckBoxEncrypt.checkState() == 2:
            #checkbox is checked, make password things available
            self.ui.LabelPassword.setEnabled(True)
            self.ui.LabelConfirmPassword.setEnabled(True)
            self.ui.LineEditPassword.setEnabled(True)
            self.ui.LineEditConfirmPassword.setEnabled(True)
            self.ui.LabelCheckPasswordMatch.setEnabled(True)
            self.ui.CheckBoxShowPassword.setEnabled(True)
            
            #check if ok/cancel should be unavailable because passwords do not match/or there is no password but the encrypt box is checked
            self.CheckIfAcceptable()
        else:
            #checkbox is unchecked, make pw things unavailable
            self.ui.LabelPassword.setEnabled(False)
            self.ui.LabelConfirmPassword.setEnabled(False)
            self.ui.LineEditPassword.setEnabled(False)
            self.ui.LineEditConfirmPassword.setEnabled(False)
            self.ui.LabelCheckPasswordMatch.setEnabled(False)
            self.ui.CheckBoxShowPassword.setEnabled(False)
            
            #check if ok/cancel should be unavailable because passwords do not match/or there is no password but the encrypt box is checked
            self.CheckIfAcceptable()
        
    #def ShowPasswordCheck(self):
        #shoulb be obsolete
        #self.ui.horizontalLayout_2.addWidget(self.ui.LabelCheckPasswordMatch)
        #self.ui.horizontalLayout_2.addWidget(self.ui.CheckBoxShowPassword)
        #self.ui.LabelCheckPasswordMatch.show()
        #self.ui.CheckBoxShowPassword.show()
        #self.CheckIfAcceptable()
    
    def CheckPasswordMatch(self):
        #checks if passwords match and are not ''
        if self.ui.LineEditPassword.text() == self.ui.LineEditConfirmPassword.text() and self.ui.LineEditPassword.text() != '':
            self.ui.LabelCheckPasswordMatch.setText(u'Passwörter sind identisch')
            self.CheckIfAcceptable()
        elif self.ui.LineEditPassword.text() == self.ui.LineEditConfirmPassword.text() and self.ui.LineEditPassword.text() == '':
            self.ui.LabelCheckPasswordMatch.setText(u'Passwort eingeben')
            self.CheckIfAcceptable()
        else:
            self.ui.LabelCheckPasswordMatch.setText(u'Passwörter sind nicht identisch')
            self.CheckIfAcceptable()
            
    def ShowPassword(self):
        #makes the password lines readable if the show passwords checkbox is checked and not readable when it is unchecked
        if self.ui.CheckBoxShowPassword.checkState() == 2:
            self.ui.LineEditPassword.setEchoMode(0)
            self.ui.LineEditConfirmPassword.setEchoMode(0)
        else:
            self.ui.LineEditPassword.setEchoMode(2)
            self.ui.LineEditConfirmPassword.setEchoMode(2)
            
    def CheckIfAcceptable(self):
        #enables the ok/cancel button only if the passwords match and are not '' when the encrypt box is checked or if the encrypt box is unchecked
        if self.ui.CheckBoxEncrypt.checkState() == 2 and self.ui.LineEditPassword.text() != u'' and self.ui.LineEditPassword.text() == self.ui.LineEditConfirmPassword.text():
            self.ui.buttonBox.setEnabled(True)
        elif self.ui.CheckBoxEncrypt.checkState() != 2:
            self.ui.buttonBox.setEnabled(True)
        else:
            self.ui.buttonBox.setEnabled(False)
            
    def Accepted(self):
        #gets the password and the db file and makes them global variables if when ok is clicked
        DBPassword = self.ui.LineEditPassword.text()
        NewSQLiteDB = self.ui.LineEditFile.text()
        #!add make new db functions
        global Password, ActiveSQLiteDB
        Password = DBPassword
        ActiveSQLiteDB = NewSQLiteDB
        #print Password, ActiveSQLiteDB


class SettingsDialog(QtGui.QDialog):
#dialog to change settings for the current file, largely the same with the new dialog, but has a few changes
    def __init__(self,SQLiteDB):
        #initializes the dialog
        QtGui.QDialog.__init__(self)
        self.ui=Ui_Dialog()
        self.ui.setupUi(self)
        #modal because working in the main window makes no sense until the file has its settings set
        self.setModal(True)
        self.setWindowTitle(u'Einstellungen der aktuellen Datenbank')
        
        #makes the password lines write only (shows stars/circles) and checks whether the active file has a password
        self.ui.LineEditPassword.setEchoMode(2)
        self.ui.LineEditConfirmPassword.setEchoMode(2)
        self.CheckForPassword()
        
        #gives the path to current db file and makes it and the change file button unavailable
        self.ui.LineEditFile.setText(SQLiteDB)
        self.ui.LineEditFile.setEnabled(False)
        self.ui.PushButtonChangeFile.setEnabled(False)
        
        #gives the encrypt db checkbox, show passwords checkbox functionality, no need for change file button function
        self.connect(self.ui.CheckBoxEncrypt,QtCore.SIGNAL(u"clicked()"),self.EncryptOptions)
        self.connect(self.ui.CheckBoxShowPassword,QtCore.SIGNAL(u"clicked()"),self.ShowPassword)
        
        #checks if the passwords match whenever one of the password lines is changed
        self.connect(self.ui.LineEditPassword,QtCore.SIGNAL(u"textEdited(QString)"),self.CheckPasswordMatch)
        self.connect(self.ui.LineEditConfirmPassword,QtCore.SIGNAL(u"textEdited(QString)"),self.CheckPasswordMatch)
        
        #if accepted (OK clicked) gives back the options
        self.connect(self,QtCore.SIGNAL(u"accepted()"),self.Accepted)
            
    def CheckForPassword(self):
        #checks if a password is provided (try in case Password is not global/doesn't exist) and if it is not or is '' deactivates the pw options
        #and sets the checkbox to unchecked, if there is one the checkbox is checked and the options are available and the password is filled into the lines
        try:
            if Password == '':
                self.ui.LabelPassword.setEnabled(False)
                self.ui.LabelConfirmPassword.setEnabled(False)
                self.ui.LineEditPassword.setEnabled(False)
                self.ui.LineEditConfirmPassword.setEnabled(False)
                self.ui.LabelCheckPasswordMatch.setEnabled(False)
                self.ui.CheckBoxShowPassword.setEnabled(False)
            else:
                self.ui.CheckBoxEncrypt.setChecked(True)
                self.ui.LineEditPassword.setText(Password)
                self.ui.LineEditConfirmPassword.setText(Password)
                self.EncryptOptions()
                self.CheckPasswordMatch()
        except:
            self.ui.LabelPassword.setEnabled(False)
            self.ui.LabelConfirmPassword.setEnabled(False)
            self.ui.LineEditPassword.setEnabled(False)
            self.ui.LineEditConfirmPassword.setEnabled(False) 
            self.ui.LabelCheckPasswordMatch.setEnabled(False)
            self.ui.CheckBoxShowPassword.setEnabled(False)
    
    def EncryptOptions(self):
        #makes the password options only active if the encrypt checkbox is checked and checks whether to make ok/cancel available
        #same as in NewDialog
        if self.ui.CheckBoxEncrypt.checkState() == 2:
            #checkbox is checked, make password things available
            self.ui.LabelPassword.setEnabled(True)
            self.ui.LabelConfirmPassword.setEnabled(True)
            self.ui.LineEditPassword.setEnabled(True)
            self.ui.LineEditConfirmPassword.setEnabled(True)
            self.ui.LabelCheckPasswordMatch.setEnabled(True)
            self.ui.CheckBoxShowPassword.setEnabled(True)
            
            #check if ok/cancel should be unavailable because passwords do not match/or there is no password but the encrypt box is checked
            self.CheckIfAcceptable()
        else:
            #checkbox is unchecked, make pw things unavailable
            self.ui.LabelPassword.setEnabled(False)
            self.ui.LabelConfirmPassword.setEnabled(False)
            self.ui.LineEditPassword.setEnabled(False)
            self.ui.LineEditConfirmPassword.setEnabled(False)
            self.ui.LabelCheckPasswordMatch.setEnabled(False)
            self.ui.CheckBoxShowPassword.setEnabled(False)
            
            #check if ok/cancel should be unavailable because passwords do not match/or there is no password but the encrypt box is checked
            self.CheckIfAcceptable()
        
    #def ShowPasswordCheck(self):
        #should be obsolete
        #self.ui.horizontalLayout_2.addWidget(self.ui.LabelCheckPasswordMatch)
        #self.ui.horizontalLayout_2.addWidget(self.ui.CheckBoxShowPassword)
        #self.ui.LabelCheckPasswordMatch.show()
        #self.ui.CheckBoxShowPassword.show()
        #self.CheckIfAcceptable()
    
    def CheckPasswordMatch(self):
        #checks if passwords match and are not ''
        #same as in NewDialog
        if self.ui.LineEditPassword.text() == self.ui.LineEditConfirmPassword.text() and self.ui.LineEditPassword.text() != '':
            self.ui.LabelCheckPasswordMatch.setText(u'Passwörter sind identisch')
            self.CheckIfAcceptable()
        elif self.ui.LineEditPassword.text() == self.ui.LineEditConfirmPassword.text() and self.ui.LineEditPassword.text() == '':
            self.ui.LabelCheckPasswordMatch.setText(u'Passwort eingeben')
            self.CheckIfAcceptable()
        else:
            self.ui.LabelCheckPasswordMatch.setText(u'Passwörter sind nicht identisch')
            self.CheckIfAcceptable()
            
    def ShowPassword(self):
        #makes the password lines readable if the show passwords checkbox is checked and not readable when it is unchecked
        #same as in NewDialog
        if self.ui.CheckBoxShowPassword.checkState() == 2:
            self.ui.LineEditPassword.setEchoMode(0)
            self.ui.LineEditConfirmPassword.setEchoMode(0)
        else:
            self.ui.LineEditPassword.setEchoMode(2)
            self.ui.LineEditConfirmPassword.setEchoMode(2)
            
    def CheckIfAcceptable(self):
        #enables the ok/cancel button only if the passwords match and are not '' when the encrypt box is checked or if the encrypt box is unchecked
        #same as in NewDialog
        if self.ui.CheckBoxEncrypt.checkState() == 2 and self.ui.LineEditPassword.text() != u'' and self.ui.LineEditPassword.text() == self.ui.LineEditConfirmPassword.text():
            self.ui.buttonBox.setEnabled(True)
        elif self.ui.CheckBoxEncrypt.checkState() != 2:
            self.ui.buttonBox.setEnabled(True)
        else:
            self.ui.buttonBox.setEnabled(False)
        
    def Accepted(self):
        #gets the password and the db file and makes them global variables if when ok is clicked
        DBPassword = self.ui.LineEditPassword.text()
        #NewSQLiteDB = self.ui.LineEditFile.text() #unnecessary in settings mode
        #!add make new db functions
        global Password #, ActiveSQLiteDB #unnecessary in settings mode.
        Password = DBPassword
        #ActiveSQLiteDB = NewSQLiteDB #unnecessary in settings mode
        #print Password, ActiveSQLiteDB
        

class AddDialog(QtGui.QDialog):
#dialog to add new data into database
    def __init__(self, AddWhat,tmpTransactions,tmpPicks):
        #initializes the dialog
        QtGui.QDialog.__init__(self)
        self.ui=Ui_AddDialog()
        self.ui.setupUi(self)
        #modal because working in the main window makes no sense until the file has its settings set
        self.setModal(True)
        #self.connect(self,QtCore.SIGNAL(u"accepted()"),self.Accepted)
        self.connect(self,QtCore.SIGNAL(u"rejected()"),self.Rejected)
        
        self.tmpTransactions = tmpTransactions
        self.tmpPicks = tmpPicks
        
        if AddWhat == 0:
            self.setWindowTitle(u'Neuen Bookie hinzufügen')
            self.connect(self,QtCore.SIGNAL(u"accepted()"),self.AcceptedBookies)
            self.ui.table2.hide()
            self.ui.table1.insertColumn(0)
            self.ui.table1.setHorizontalHeaderItem(0,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(0).setText(u'Name/www')
            self.ui.table1.insertColumn(1)
            self.ui.table1.setHorizontalHeaderItem(1,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(1).setText(u'Bonus?')
            self.ui.table1.insertColumn(2)
            self.ui.table1.setHorizontalHeaderItem(2,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(2).setText(u'Bonusumsatzbedingung')
            self.ui.table1.insertColumn(3)
            self.ui.table1.setHorizontalHeaderItem(3,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(3).setText(u'Bonusquotenbedingung')
            self.ui.table1.insertRow(0)
            self.exec_()
        elif AddWhat == 1:
            self.setWindowTitle(u'Neue Transaktion hinzufügen')
            self.connect(self,QtCore.SIGNAL(u"accepted()"),self.AcceptedTransactions)
            self.ui.table2.hide()
            self.ui.table1.insertColumn(0)
            self.ui.table1.setHorizontalHeaderItem(0,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(0).setText(u'Bookieid')
            self.ui.table1.insertColumn(1)
            self.ui.table1.setHorizontalHeaderItem(1,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(1).setText(u'Datum/Uhrzeit')
            self.ui.table1.insertColumn(2)
            self.ui.table1.setHorizontalHeaderItem(2,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(2).setText(u'Betrag')
            self.ui.table1.insertColumn(3)
            self.ui.table1.setHorizontalHeaderItem(3,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(3).setText(u'Kategorie')
            self.ui.table1.insertRow(0)
            self.ui.table1.setItem(0,1,QtGui.QTableWidgetItem())
            self.ui.table1.item(0,1).setText(unicode(datetime.datetime.today()))
            self.exec_()
        elif AddWhat == 2:
            self.setWindowTitle(u'Neue Wette hinzufügen')
            self.connect(self,QtCore.SIGNAL(u"accepted()"),self.AcceptedBets)
            self.ui.table1.insertColumn(0)
            self.ui.table1.setHorizontalHeaderItem(0,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(0).setText(u'Bookie')
            self.ui.table1.insertColumn(1)
            self.ui.table1.setHorizontalHeaderItem(1,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(1).setText(u'Datum/Zeit Pick')
            self.ui.table1.insertColumn(2)
            self.ui.table1.setHorizontalHeaderItem(2,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(2).setText(u'Datum/Zeit Event')
            self.ui.table1.insertColumn(3)
            self.ui.table1.setHorizontalHeaderItem(3,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(3).setText(u'Kategorie')
            self.ui.table1.insertColumn(4)
            self.ui.table1.setHorizontalHeaderItem(4,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(4).setText(u'Sport')          
            self.ui.table1.insertColumn(5)
            self.ui.table1.setHorizontalHeaderItem(5,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(5).setText(u'Liga')
            self.ui.table1.insertColumn(6)
            self.ui.table1.setHorizontalHeaderItem(6,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(6).setText(u'Heimteam')
            self.ui.table1.insertColumn(7)
            self.ui.table1.setHorizontalHeaderItem(7,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(7).setText(u'Auswärtsteam')
            self.ui.table1.insertColumn(8)
            self.ui.table1.setHorizontalHeaderItem(8,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(8).setText(u'Art des Picks')
            self.ui.table1.insertColumn(9)
            self.ui.table1.setHorizontalHeaderItem(9,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(9).setText(u'Spieler')
            self.ui.table1.insertColumn(10)
            self.ui.table1.setHorizontalHeaderItem(10,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(10).setText(u'Pick')
            self.ui.table1.insertColumn(11)
            self.ui.table1.setHorizontalHeaderItem(11,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(11).setText(u'Quote')
            self.ui.table1.insertColumn(12)
            self.ui.table1.setHorizontalHeaderItem(12,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(12).setText(u'Quote Kombi')
            self.ui.table1.insertColumn(13)
            self.ui.table1.setHorizontalHeaderItem(13,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(13).setText(u'Begründung')
            self.ui.table1.insertRow(0)
            self.ui.table1.setItem(0,0,QtGui.QTableWidgetItem())
            self.ui.table1.item(0,0).setText(u'')
            self.ui.table1.setItem(0,1,QtGui.QTableWidgetItem())
            self.ui.table1.item(0,1).setText(unicode(datetime.datetime.today()))
            self.ui.table1.setItem(0,2,QtGui.QTableWidgetItem())
            self.ui.table1.item(0,2).setText(unicode(datetime.datetime.today()))
            self.ui.table1.setItem(0,3,QtGui.QTableWidgetItem())
            self.ui.table1.item(0,3).setText(u'')
            self.ui.table1.setItem(0,4,QtGui.QTableWidgetItem())
            self.ui.table1.item(0,4).setText(u'')
            self.ui.table1.setItem(0,5,QtGui.QTableWidgetItem())
            self.ui.table1.item(0,5).setText(u'')
            self.ui.table1.setItem(0,6,QtGui.QTableWidgetItem())
            self.ui.table1.item(0,6).setText(u'')
            self.ui.table1.setItem(0,7,QtGui.QTableWidgetItem())
            self.ui.table1.item(0,7).setText(u'')
            self.ui.table1.setItem(0,8,QtGui.QTableWidgetItem())
            self.ui.table1.item(0,8).setText(u'')
            self.ui.table1.setItem(0,9,QtGui.QTableWidgetItem())
            self.ui.table1.item(0,9).setText(u'')
            self.ui.table1.setItem(0,10,QtGui.QTableWidgetItem())
            self.ui.table1.item(0,10).setText(u'')
            self.ui.table1.setItem(0,11,QtGui.QTableWidgetItem())
            self.ui.table1.item(0,11).setText(u'')
            self.ui.table1.setItem(0,12,QtGui.QTableWidgetItem())
            self.ui.table1.item(0,12).setText(u'')
            self.ui.table1.setItem(0,13,QtGui.QTableWidgetItem())
            self.ui.table1.item(0,13).setText(u'')
            self.ui.table2.insertColumn(0)
            self.ui.table2.setHorizontalHeaderItem(0,QtGui.QTableWidgetItem())
            self.ui.table2.horizontalHeaderItem(0).setText(u'Bookie')
            self.ui.table2.insertColumn(1)
            self.ui.table2.setHorizontalHeaderItem(1,QtGui.QTableWidgetItem())
            self.ui.table2.horizontalHeaderItem(1).setText(u'Datum/Zeit')
            self.ui.table2.insertColumn(2)
            self.ui.table2.setHorizontalHeaderItem(2,QtGui.QTableWidgetItem())
            self.ui.table2.horizontalHeaderItem(2).setText(u'Wettart')
            self.ui.table2.insertColumn(3)
            self.ui.table2.setHorizontalHeaderItem(3,QtGui.QTableWidgetItem())
            self.ui.table2.horizontalHeaderItem(3).setText(u'Kategorie')
            self.ui.table2.insertColumn(4)
            self.ui.table2.setHorizontalHeaderItem(4,QtGui.QTableWidgetItem())
            self.ui.table2.horizontalHeaderItem(4).setText(u'Quote')          
            self.ui.table2.insertColumn(5)
            self.ui.table2.setHorizontalHeaderItem(5,QtGui.QTableWidgetItem())
            self.ui.table2.horizontalHeaderItem(5).setText(u'Einsatz')
            self.ui.table2.insertRow(0)
            self.ui.table2.setItem(0,0,QtGui.QTableWidgetItem())
            self.ui.table2.item(0,0).setText(u'')
            self.ui.table2.setItem(0,1,QtGui.QTableWidgetItem())
            self.ui.table2.item(0,1).setText(unicode(datetime.datetime.today()))
            self.ui.table2.setItem(0,2,QtGui.QTableWidgetItem())
            self.ui.table2.item(0,2).setText(u'')
            self.ui.table2.setItem(0,3,QtGui.QTableWidgetItem())
            self.ui.table2.item(0,3).setText(u'')
            self.ui.table2.setItem(0,4,QtGui.QTableWidgetItem())
            self.ui.table2.item(0,4).setText(u'')
            self.ui.table2.setItem(0,5,QtGui.QTableWidgetItem())
            self.ui.table2.item(0,5).setText(u'')
            self.exec_()
        elif AddWhat == 3:
            self.setWindowTitle(u'Neuen Pick hinzufügen')
            self.connect(self,QtCore.SIGNAL(u"accepted()"),self.AcceptedPicks)
            self.ui.table2.hide()
            self.ui.table1.insertColumn(0)
            self.ui.table1.setHorizontalHeaderItem(0,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(0).setText(u'Bookie')
            self.ui.table1.insertColumn(1)
            self.ui.table1.setHorizontalHeaderItem(1,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(1).setText(u'Datum/Zeit Pick')
            self.ui.table1.insertColumn(2)
            self.ui.table1.setHorizontalHeaderItem(2,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(2).setText(u'Datum/Zeit Event')
            self.ui.table1.insertColumn(3)
            self.ui.table1.setHorizontalHeaderItem(3,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(3).setText(u'Kategorie')
            self.ui.table1.insertColumn(4)
            self.ui.table1.setHorizontalHeaderItem(4,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(4).setText(u'Sport')          
            self.ui.table1.insertColumn(5)
            self.ui.table1.setHorizontalHeaderItem(5,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(5).setText(u'Liga')
            self.ui.table1.insertColumn(6)
            self.ui.table1.setHorizontalHeaderItem(6,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(6).setText(u'Heimteam')
            self.ui.table1.insertColumn(7)
            self.ui.table1.setHorizontalHeaderItem(7,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(7).setText(u'Auswärtsteam')
            self.ui.table1.insertColumn(8)
            self.ui.table1.setHorizontalHeaderItem(8,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(8).setText(u'Art des Picks')
            self.ui.table1.insertColumn(9)
            self.ui.table1.setHorizontalHeaderItem(9,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(9).setText(u'Spieler')
            self.ui.table1.insertColumn(10)
            self.ui.table1.setHorizontalHeaderItem(10,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(10).setText(u'Pick')
            self.ui.table1.insertColumn(11)
            self.ui.table1.setHorizontalHeaderItem(11,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(11).setText(u'Quote')
            self.ui.table1.insertColumn(12)
            self.ui.table1.setHorizontalHeaderItem(12,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(12).setText(u'Quote Kombi')
            self.ui.table1.insertColumn(13)
            self.ui.table1.setHorizontalHeaderItem(13,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(13).setText(u'Begründung')
            self.ui.table1.insertRow(0)
            self.ui.table1.setItem(0,0,QtGui.QTableWidgetItem())
            self.ui.table1.item(0,0).setText(u'')
            self.ui.table1.setItem(0,1,QtGui.QTableWidgetItem())
            self.ui.table1.item(0,1).setText(unicode(datetime.datetime.today()))
            self.ui.table1.setItem(0,2,QtGui.QTableWidgetItem())
            self.ui.table1.item(0,2).setText(unicode(datetime.datetime.today()))
            self.ui.table1.setItem(0,3,QtGui.QTableWidgetItem())
            self.ui.table1.item(0,3).setText(u'')
            self.ui.table1.setItem(0,4,QtGui.QTableWidgetItem())
            self.ui.table1.item(0,4).setText(u'')
            self.ui.table1.setItem(0,5,QtGui.QTableWidgetItem())
            self.ui.table1.item(0,5).setText(u'')
            self.ui.table1.setItem(0,6,QtGui.QTableWidgetItem())
            self.ui.table1.item(0,6).setText(u'')
            self.ui.table1.setItem(0,7,QtGui.QTableWidgetItem())
            self.ui.table1.item(0,7).setText(u'')
            self.ui.table1.setItem(0,8,QtGui.QTableWidgetItem())
            self.ui.table1.item(0,8).setText(u'')
            self.ui.table1.setItem(0,9,QtGui.QTableWidgetItem())
            self.ui.table1.item(0,9).setText(u'')
            self.ui.table1.setItem(0,10,QtGui.QTableWidgetItem())
            self.ui.table1.item(0,10).setText(u'')
            self.ui.table1.setItem(0,11,QtGui.QTableWidgetItem())
            self.ui.table1.item(0,11).setText(u'')
            self.ui.table1.setItem(0,12,QtGui.QTableWidgetItem())
            self.ui.table1.item(0,12).setText(u'')
            self.ui.table1.setItem(0,13,QtGui.QTableWidgetItem())
            self.ui.table1.item(0,13).setText(u'')
            self.exec_()
        elif AddWhat == 4:
            self.setWindowTitle(u'Neue Kategorie hinzufügen')
            self.connect(self,QtCore.SIGNAL(u"accepted()"),self.AcceptedCategories)
            self.ui.table2.hide()
            self.ui.table1.insertColumn(0)
            self.ui.table1.setHorizontalHeaderItem(0,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(0).setText(u'Kategorie')
            self.ui.table1.insertRow(0)
            self.exec_()
        elif AddWhat == 5:
            self.setWindowTitle(u'Neuen Sport/neue Liga hinzufügen')
            self.connect(self,QtCore.SIGNAL(u"accepted()"),self.AcceptedSportsLeagues)
            self.ui.table2.hide()
            self.ui.table1.insertColumn(0)
            self.ui.table1.setHorizontalHeaderItem(0,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(0).setText(u'Sport')
            self.ui.table1.insertColumn(1)
            self.ui.table1.setHorizontalHeaderItem(1,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(1).setText(u'Liga')
            self.ui.table1.insertRow(0)
            self.exec_()
        elif AddWhat == 6:
            self.setWindowTitle(u'Neuen Sport/neue Pickart hinzufügen')
            self.connect(self,QtCore.SIGNAL(u"accepted()"),self.AcceptedSportsBetTypes)
            self.ui.table2.hide()
            self.ui.table1.insertColumn(0)
            self.ui.table1.setHorizontalHeaderItem(0,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(0).setText(u'Sport')
            self.ui.table1.insertColumn(1)
            self.ui.table1.setHorizontalHeaderItem(1,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(1).setText(u'Pick Art')
            self.ui.table1.insertRow(0)
            self.exec_()
        elif AddWhat == 7:
            self.setWindowTitle(u'Neue Wettart hinzufügen')
            self.connect(self,QtCore.SIGNAL(u"accepted()"),self.AcceptedBetTypes)
            self.ui.table2.hide()
            self.ui.table1.insertColumn(0)
            self.ui.table1.setHorizontalHeaderItem(0,QtGui.QTableWidgetItem())
            self.ui.table1.horizontalHeaderItem(0).setText(u'Wettart')
            self.ui.table1.insertRow(0)
            self.exec_()
            
    def AcceptedBookies(self):
        name = unicode(self.ui.table1.item(0,0).text())
        bonus = unicode(self.ui.table1.item(0,1).text())
        bonusrev = unicode(self.ui.table1.item(0,2).text())
        bonusodds = unicode(self.ui.table1.item(0,3).text())
        global AddBookies
        AddBookies = [(None,name,0,0,0,bonus,bonusrev,bonusodds,0,u'',u'',),]
        global added
        added = 1
        
    def AcceptedTransactions(self):
        bookieid = unicode(self.ui.table1.item(0,0).text())
        datetime = unicode(self.ui.table1.item(0,1).text())
        money = unicode(self.ui.table1.item(0,2).text())
        category = unicode(self.ui.table1.item(0,3).text())
        global AddTransactions
        AddTransactions = [(None,bookieid,datetime,money,category,),]
        global added
        added = 1
        
    def AcceptedBets(self):
        bid = unicode(self.ui.table1.item(0,0).text())
        datetime = unicode(self.ui.table1.item(0,1).text())
        datetimebegin = unicode(self.ui.table1.item(0,2).text())
        category = unicode(self.ui.table1.item(0,3).text())
        sport = unicode(self.ui.table1.item(0,4).text())
        league = unicode(self.ui.table1.item(0,5).text())
        hometeam = unicode(self.ui.table1.item(0,6).text())
        awayteam = unicode(self.ui.table1.item(0,7).text())
        picktype = unicode(self.ui.table1.item(0,8).text())
        player = unicode(self.ui.table1.item(0,9).text())
        pick = unicode(self.ui.table1.item(0,10).text())
        odds = unicode(self.ui.table1.item(0,11).text())
        oddsmulti = unicode(self.ui.table1.item(0,12).text())
        reason = unicode(self.ui.table1.item(0,13).text())
        global AddPicks
        AddPicks = [(None,bid,datetime,datetimebegin,category,sport,league,hometeam,awayteam,picktype,player,pick,odds,oddsmulti,reason,u'',u'',)]
        bidb = unicode(self.ui.table2.item(0,0).text())
        datetimeb = unicode(self.ui.table2.item(0,1).text())
        bettypeid = unicode(self.ui.table2.item(0,2).text())
        categoryb = unicode(self.ui.table2.item(0,3).text())
        oddsb = unicode(self.ui.table2.item(0,4).text())
        stake = unicode(self.ui.table2.item(0,5).text())
        global AddBets
        AddBets = [(None,bidb,len(self.tmpTransactions)+1,datetimeb,bettypeid,len(self.tmpPicks)+1,categoryb,oddsb,stake,u'',u'',)]
        global AddTransactions
        AddTransactions = [(None,bidb,datetimeb,u'-'+stake,u'Einsatz')]
        global added
        added = 1
        
    def AcceptedPicks(self):
        bid = unicode(self.ui.table1.item(0,0).text())
        datetime = unicode(self.ui.table1.item(0,1).text())
        datetimebegin = unicode(self.ui.table1.item(0,2).text())
        category = unicode(self.ui.table1.item(0,3).text())
        sport = unicode(self.ui.table1.item(0,4).text())
        league = unicode(self.ui.table1.item(0,5).text())
        hometeam = unicode(self.ui.table1.item(0,6).text())
        awayteam = unicode(self.ui.table1.item(0,7).text())
        picktype = unicode(self.ui.table1.item(0,8).text())
        player = unicode(self.ui.table1.item(0,9).text())
        pick = unicode(self.ui.table1.item(0,10).text())
        odds = unicode(self.ui.table1.item(0,11).text())
        oddsmulti = unicode(self.ui.table1.item(0,12).text())
        reason = unicode(self.ui.table1.item(0,13).text())
        global AddPicks
        AddPicks = [(None,bid,datetime,datetimebegin,category,sport,league,hometeam,awayteam,picktype,player,pick,odds,oddsmulti,reason,u'',u'',)]
        global added
        added = 1
        
    def AcceptedCategories(self):
        category = unicode(self.ui.table1.item(0,0).text())
        global AddCategories
        AddCategories = [(None,category,),]
        global added
        added = 1
        
    def AcceptedSportsLeagues(self):
        sport = unicode(self.ui.table1.item(0,0).text())
        league = unicode(self.ui.table1.item(0,1).text())
        global AddSportsLeagues
        AddSportsLeagues = [(None,sport,league),]
        global added
        added = 1
    
    def AcceptedSportsBetTypes(self):
        sport = unicode(self.ui.table1.item(0,0).text())
        bettype = unicode(self.ui.table1.item(0,1).text())
        global AddSportsBetTypes
        AddSportsBetTypes = [(None,sport,bettype),]
        global added
        added = 1
        
    def AcceptedBetTypes(self):
        bettype = unicode(self.ui.table1.item(0,0).text())
        global AddBetTypes
        AddBetTypes = [(None,bettype,),]
        global added
        added = 1
    
    def Rejected(self):
        global added
        added = 0

def Main():
    #function to run the program
    app = QtGui.QApplication(sys.argv)
    window=MainWindow()
    window.show()
    try:
        sys.exit(app.exec_())
    finally:
        #make a temporary copy of the current ramdb
        datetimestring = datetime.datetime.strftime(datetime.datetime.now(), u'%Y-%m-%d-%H-%M-%S')
        window.SaveMainWindowSettings()
        try:
            window.DB.CopyDB(window.CorrectPathToFile(ActiveSQLiteDB))
            window.DB.CloseConnection(window.DB.ramdb)
            return 0
        except NameError:
            window.DB.CloseConnection(window.DB.ramdb)
            return 0
    
    

#runs the program
if __name__ == u"__main__":
     Main()
